﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OscarClient;

namespace CommonHelper
{
    public class OscarHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string ConnectionString = "Server={0};Port={1};User Id={2};Password={3};Database={4};Encoding=GBK;"; 

        public OscarHelper(string address, string dataName, string name, string pass, string port)
        {
            ConnectionString = string.Format(ConnectionString, address, port, name, pass, dataName);
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="sqlList">Sql文件列表（按照1,2,3,4,5...的先后顺序执行）</param>
        public void CreateOscarDataBase(IList<StringBuilder> sqlList)
        {
            DeleteSchema();
            if (sqlList != null && sqlList.Count > 0)
            {
                foreach (var stringBuilder in sqlList)
                {
                    ExecuteSql(stringBuilder);
                }
            }
        }

        /// <summary>
        /// 执行Sql
        /// </summary>
        /// <param name="stringBuilder">一个Sql文件的所有Sql</param>
        private void ExecuteSql(StringBuilder stringBuilder)
        {
            OscarConnection conn = new OscarConnection(ConnectionString);
            conn.Open();
            OscarTransaction sqlTransaction = conn.BeginTransaction();
            try
            {
                OscarCommand cmd = new OscarCommand();
                cmd.Connection = conn;
                // 建立事务
                cmd.Transaction = sqlTransaction;
                cmd.CommandText = stringBuilder.ToString();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                sqlTransaction.Rollback();
                throw new Exception("创建数据库失败" + ex.Message);
            }
            finally 
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 删除WordSolution模式
        /// </summary>
        private void DeleteSchema()
        {
            OscarConnection conn = new OscarConnection(ConnectionString);
            conn.Open();
            try
            {
                OscarCommand cmd = new OscarCommand();
                cmd.Connection = conn;
                cmd.CommandText = "drop schema WordSolution cascade;";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // 不管是否存在该模式，吞掉异常。
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
